from sqlalchemy import exc, func, and_
from sqlalchemy.orm import Session, joinedload

from model.ParamModel import SysDictDetailModel, SysDictModel
from schema.ParamSchema import SysDictInfo, SysDictDetailInfo
from schema.QuerySchema import QueryBase
from util.ErrorCode import ErrorCode
from util.LogHelper import Log


def add_sys_dict(sysDictInfo: SysDictInfo, db: Session):
    sysDictModel = SysDictModel(sys_dict_id=sysDictInfo.sys_dict_id, sys_dict_name=sysDictInfo.sys_dict_name)
    try:
        db.add(sysDictModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_sys_dict failed:{sysDictInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_sys_dict(sysDictInfo: SysDictInfo, db: Session):
    sysDictModel = db.query(SysDictModel).filter(SysDictModel.sys_dict_id == sysDictInfo.sys_dict_id).one_or_none()
    if sysDictModel is not None:
        if sysDictInfo.sys_dict_name is not None:
            sysDictModel.sys_dict_name = sysDictInfo.sys_dict_name if len(
                sysDictInfo.sys_dict_name) != 0 else sysDictModel.sys_dict_name
        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_sys_dict failed:{sysDictInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'sys_dict_id:{sysDictInfo.sys_dict_id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_sys_dict_by_id(sys_dict_id: int, db: Session):
    try:
        result = db.query(SysDictModel).filter(SysDictModel.sys_dict_id == sys_dict_id).one_or_none()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_sys_dict failed! sys_dict_id: {sys_dict_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_sys_dict_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(SysDictModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit

        result = db.query(SysDictModel).order_by(SysDictModel.sys_dict_id).offset((query_base.skip - 1) * query_base.limit).limit(query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count, 'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_sys_dict_by_page failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_sys_dict_all_detail(db: Session):
    try:
        result = db.query(SysDictModel).options(joinedload(SysDictModel.sys_dict_details)).order_by(
            SysDictModel.sys_dict_id).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_sys_dict_all_detail failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def delete_sys_dict(sys_dict_id: int, db: Session):
    try:
        sysDictModel = db.query(SysDictModel).filter(SysDictModel.sys_dict_id == sys_dict_id).delete()
        db.commit()
        if sysDictModel == 0:
            return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}
        else:
            return {'code': ErrorCode.SUCCESS.value, 'message': 'sucess'}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'delete_sys_dict failed! sys_dict_id: {sys_dict_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def add_sys_dict_detail(sysDictDetailInfo: SysDictDetailInfo, db: Session):
    try:
        sys_dict_item_id_max = db.query(func.max(SysDictDetailModel.sys_dict_item_id)).filter(
            SysDictDetailModel.sys_dict_id == sysDictDetailInfo.sys_dict_id).one()
        if sys_dict_item_id_max[0] is not None:
            sys_dict_item_id = sys_dict_item_id_max[0] + 1
        else:
            sys_dict_item_id = 1

        sysDictDetailModel = SysDictDetailModel(sys_dict_id=sysDictDetailInfo.sys_dict_id,
                                                sys_dict_item_id=sys_dict_item_id,
                                                sys_dict_item_name=sysDictDetailInfo.sys_dict_item_name)

        db.add(sysDictDetailModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_sys_dict_detail failed: {sysDictDetailInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_sys_dict_detail(sysDictDetailInfo: SysDictDetailInfo, db: Session):
    sysDictDetailModel = db.query(SysDictDetailModel).filter(
        and_(SysDictDetailModel.sys_dict_id == sysDictDetailInfo.sys_dict_id,
             SysDictDetailModel.sys_dict_item_id == sysDictDetailInfo.sys_dict_item_id)).one_or_none()
    if sysDictDetailModel is not None:
        if sysDictDetailInfo.sys_dict_item_name is not None:
            sysDictDetailModel.sys_dict_item_name = sysDictDetailInfo.sys_dict_item_name if len(
                sysDictDetailInfo.sys_dict_item_name) != 0 else sysDictDetailModel.sys_dict_item_name
        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_sys_dict_detail failed:{sysDictDetailInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'sys_dict_item_id:{sysDictDetailInfo.sys_dict_item_name} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_sys_dict_detail_by_id(sys_dict_id: int, db: Session):
    try:
        result = db.query(SysDictDetailModel).filter(SysDictDetailModel.sys_dict_id == sys_dict_id).order_by(
            SysDictDetailModel.sys_dict_item_id).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_sys_dict_items_by_id failed! sys_dict_id: {sys_dict_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def delete_sys_dict_detail(sysDictDetailInfo: SysDictDetailInfo, db: Session):
    try:
        sysDictDetailModel = db.query(SysDictDetailModel).filter(
            and_(SysDictDetailModel.sys_dict_id == sysDictDetailInfo.sys_dict_id,
                 SysDictDetailModel.sys_dict_item_id == sysDictDetailInfo.sys_dict_item_id)).delete()
        db.commit()
        if sysDictDetailModel == 0:
            return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}
        else:
            return {'code': ErrorCode.SUCCESS.value, 'message': 'sucess'}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'delete_sys_dict_detail failed! sys_dict_id: {sysDictDetailInfo.sys_dict_id} sys_dict_item_id: {sysDictDetailInfo.sys_dict_item_id} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
